/*
 Navicat MySQL Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50739 (5.7.39)
 Source Host           : localhost:3306
 Source Schema         : fellowmark_schema

 Target Server Type    : MySQL
 Target Server Version : 50739 (5.7.39)
 File Encoding         : 65001

 Date: 28/02/2023 22:22:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for assignment
-- ----------------------------
DROP TABLE IF EXISTS `assignment`;
CREATE TABLE `assignment`  (
                               `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                               `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                               `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                               `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                               `module_id` int(10) UNSIGNED NOT NULL,
                               `groupsize` int(11) NOT NULL DEFAULT 1,
                               `deadline` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                               PRIMARY KEY (`id`) USING BTREE,
                               UNIQUE INDEX `name`(`name`, `module_id`) USING BTREE,
                               INDEX `module_id`(`module_id`) USING BTREE,
                               CONSTRAINT `assignment_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `module` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of assignment
-- ----------------------------
INSERT INTO `assignment` VALUES (1, current_timestamp(), current_timestamp(), 'assignment-1', 1, 1, '2023-02-08');
INSERT INTO `assignment` VALUES (2, current_timestamp(), current_timestamp(), 'assignment-2', 1, 1, '2023-02-25');

-- ----------------------------
-- Table structure for enrollment
-- ----------------------------
DROP TABLE IF EXISTS `enrollment`;
CREATE TABLE `enrollment`  (
                               `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                               `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                               `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                               `module_id` int(10) UNSIGNED NOT NULL,
                               `student_id` int(10) UNSIGNED NOT NULL,
                               PRIMARY KEY (`id`) USING BTREE,
                               UNIQUE INDEX `module_id`(`module_id`, `student_id`) USING BTREE,
                               INDEX `student_id`(`student_id`) USING BTREE,
                               CONSTRAINT `enrollment_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `module` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                               CONSTRAINT `enrollment_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of enrollment
-- ----------------------------
INSERT INTO `enrollment` VALUES (1, current_timestamp(), current_timestamp(), 1, 26);
INSERT INTO `enrollment` VALUES (2, current_timestamp(), current_timestamp(), 1, 25);
INSERT INTO `enrollment` VALUES (3, current_timestamp(), current_timestamp(), 1, 24);
INSERT INTO `enrollment` VALUES (4, current_timestamp(), current_timestamp(), 1, 27);
INSERT INTO `enrollment` VALUES (5, current_timestamp(), current_timestamp(), 1, 28);

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
                          `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                          `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                          `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                          `pairing_id` int(10) UNSIGNED NOT NULL,
                          `rubric_id` int(10) UNSIGNED NOT NULL,
                          `grade` int(11) NOT NULL,
                          `comment` varchar(1000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
                          `submission_id` int(11) NULL DEFAULT NULL,
                          PRIMARY KEY (`id`) USING BTREE,
                          UNIQUE INDEX `pairing_id`(`pairing_id`, `rubric_id`) USING BTREE,
                          INDEX `rubric_id`(`rubric_id`) USING BTREE,
                          CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`pairing_id`) REFERENCES `pairing` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                          CONSTRAINT `grade_ibfk_2` FOREIGN KEY (`rubric_id`) REFERENCES `rubric` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (3, current_timestamp(), current_timestamp(), 37, 1, 90, 'good!', 1);

-- ----------------------------
-- Table structure for review
-- ----------------------------
DROP TABLE IF EXISTS `review`;
CREATE TABLE `review`  (
                           `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                           `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                           `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                           `pairing_id` int(10) UNSIGNED NOT NULL,
                           `submission_id` int(11) NULL DEFAULT NULL,
                           `review_pdf_annot` LONGBLOB,
                           PRIMARY KEY (`id`) USING BTREE,
                           UNIQUE INDEX `pairing_id`(`pairing_id`, `submission_id`) USING BTREE,
                           CONSTRAINT `review_ibfk_1` FOREIGN KEY (`pairing_id`) REFERENCES `pairing` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for module
-- ----------------------------
DROP TABLE IF EXISTS `module`;
CREATE TABLE `module`  (
                           `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                           `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                           `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                           `code` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                           `semester` varchar(40) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                           `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                           PRIMARY KEY (`id`) USING BTREE,
                           UNIQUE INDEX `code`(`code`) USING BTREE,
                           UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of module
-- ----------------------------
INSERT INTO `module` VALUES (1, current_timestamp(), current_timestamp(), 'IT5001', 'AY22/23 S1', 'Python');

-- ----------------------------
-- Table structure for pairing
-- ----------------------------
DROP TABLE IF EXISTS `pairing`;
CREATE TABLE `pairing` (
                           `id` int unsigned NOT NULL AUTO_INCREMENT,
                           `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                           `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                           `assignment_id` int unsigned NOT NULL,
                           `student_id` int unsigned NOT NULL,
                           `marker_id` int unsigned NOT NULL,
                           `active` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0: not reviewed; 1: reviewed; 2: review voided; 3: pending challenge request',
                           `challenge_reason` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
                           PRIMARY KEY (`id`) USING BTREE,
                           UNIQUE KEY `assignment_id` (`assignment_id`,`student_id`,`marker_id`) USING BTREE,
                           KEY `student_id` (`student_id`) USING BTREE,
                           KEY `marker_id` (`marker_id`) USING BTREE,
                           CONSTRAINT `pairing_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `assignment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                           CONSTRAINT `pairing_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                           CONSTRAINT `pairing_ibfk_3` FOREIGN KEY (`marker_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=236 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of pairing
-- ----------------------------
BEGIN;
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (221, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 24, 28, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (222, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 24, 25, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (223, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 24, 27, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (224, '2023-03-14 23:05:59', '2023-09-03 20:01:34', 2, 25, 24, 1, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (225, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 25, 26, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (226, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 25, 28, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (227, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 26, 25, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (228, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 26, 27, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (229, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 26, 24, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (230, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 27, 26, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (231, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 27, 28, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (232, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 27, 25, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (233, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 28, 27, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (234, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 28, 24, 0, NULL);
INSERT INTO `pairing` (`id`, `created_at`, `updated_at`, `assignment_id`, `student_id`, `marker_id`, `active`, `challenge_reason`) VALUES (235, '2023-03-14 23:05:59', '2023-03-14 23:05:59', 2, 28, 26, 0, NULL);
COMMIT;

-- ----------------------------
-- Table structure for question
-- ----------------------------
DROP TABLE IF EXISTS `question`;
CREATE TABLE `question`  (
                             `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                             `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                             `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
--   `question_number` int(10) UNSIGNED NOT NULL,
                             `question_text` varchar(1000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                             `assignment_id` int(10) UNSIGNED NOT NULL,
                             `start_date` TIMESTAMP NULL DEFAULT NULL,
                             `end_date` TIMESTAMP NULL DEFAULT NULL,
                             PRIMARY KEY (`id`) USING BTREE,
--   UNIQUE INDEX `question_number`(`question_number`, `assignment_id`) USING BTREE,
                             INDEX `assignment_id`(`assignment_id`) USING BTREE,
                             CONSTRAINT `question_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `assignment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of question
-- ----------------------------
INSERT INTO `question` VALUES (1, current_timestamp(), current_timestamp(), 'question1', 1, '2023-02-23', '2023-03-01');
INSERT INTO `question` VALUES (2, current_timestamp(), current_timestamp(), 'question2', 1, '2023-02-27', '2023-03-01');
INSERT INTO `question` VALUES (3, current_timestamp(), current_timestamp(), 'question3', 1, '2023-01-01', '2023-02-02');

-- ----------------------------
-- Table structure for rubric
-- ----------------------------
DROP TABLE IF EXISTS `rubric`;
CREATE TABLE `rubric`  (
                           `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                           `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                           `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                           `question_id` int(10) UNSIGNED NOT NULL,
                           `criteria` varchar(1000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                           `description` varchar(5000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                           `min_mark` int(11) NULL DEFAULT 0,
                           `max_mark` int(11) NULL DEFAULT 10,
                           PRIMARY KEY (`id`) USING BTREE,
                           INDEX `question_id`(`question_id`) USING BTREE,
                           CONSTRAINT `rubric_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of rubric
-- ----------------------------
INSERT INTO `rubric` VALUES (1, current_timestamp(), current_timestamp(), 1, 'criteria 1', 'q1 desc', 0, 100);
INSERT INTO `rubric` VALUES (2, current_timestamp(), current_timestamp(), 2, 'criteria 2', 'q2 desc', 0, 100);
INSERT INTO `rubric` VALUES (3, current_timestamp(), current_timestamp(), 3, 'criteria 3', 'q3 desc', 0, 100);

-- ----------------------------
-- Table structure for submission
-- ----------------------------
DROP TABLE IF EXISTS `submission`;
CREATE TABLE `submission`  (
                               `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                               `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                               `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                               `submitted_by_student_id` int(10) UNSIGNED NOT NULL,
--   `question_id` int(10) UNSIGNED NOT NULL,
                               `content_file_loc` varchar(1000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
--   `content` varchar(5000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
                               `assignment_id` int(11) NOT NULL,
                               PRIMARY KEY (`id`) USING BTREE,
                               INDEX `submitted_by_student_id`(`submitted_by_student_id`) USING BTREE
--   INDEX `question_id`(`question_id`) USING BTREE,
--   CONSTRAINT `submission_ibfk_1` FOREIGN KEY (`submitted_by_student_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
--   CONSTRAINT `submission_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of submission
-- ----------------------------
INSERT INTO `submission` VALUES (2, current_timestamp(), current_timestamp(), 27, '63fca899e2674e7aa269c6eb', 1);
INSERT INTO `submission` VALUES (3, current_timestamp(), current_timestamp(), 28, '63fca8e3bc4b144fd7535ebc', 1);

-- ----------------------------
-- Table structure for supervision
-- ----------------------------
DROP TABLE IF EXISTS `supervision`;
CREATE TABLE `supervision`  (
                                `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                                `module_id` int(10) UNSIGNED NOT NULL,
                                `staff_id` int(10) UNSIGNED NOT NULL,
                                `ta_bool` int(1) NOT NULL DEFAULT 0 COMMENT 'is staff a TA? 0-no 1-yes',
                                PRIMARY KEY (`id`) USING BTREE,
                                UNIQUE INDEX `module_id`(`module_id`, `staff_id`) USING BTREE,
                                INDEX `staff_id`(`staff_id`) USING BTREE,
                                CONSTRAINT `supervision_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `module` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
                                CONSTRAINT `supervision_ibfk_2` FOREIGN KEY (`staff_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of supervision
-- ----------------------------
INSERT INTO `supervision` VALUES (1, current_timestamp(), current_timestamp(), 1, 7, 0);
INSERT INTO `supervision` VALUES (2, current_timestamp(), current_timestamp(), 1, 29, 1);

-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu`  (
                             `id` int(11) NOT NULL AUTO_INCREMENT,
                             `pid` int(11) NOT NULL COMMENT 'parent ID',
                             `title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'content',
                             `url` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'url',
                             `icon` varchar(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'menu icon',
                             `code` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Authorization identifier: for backend control class definition. Example:user:list',
                             `type` int(1) NOT NULL DEFAULT 0 COMMENT 'Permission Type: 1-Catalog | 2-Menu-Main Menu | 3-Button | 5-Left Menu',
                             `ord` int(11) NOT NULL DEFAULT 0 COMMENT 'Menu Sort The bigger the value, the higher the front',
                             `status` int(1) NOT NULL DEFAULT 0 COMMENT 'Status: 0-normal | 1-banned | 2-normal with no bans',
                             `deleted` int(1) NOT NULL DEFAULT 0 COMMENT 'is deleted? 0-no 1-yes',
                             `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
                             `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
                             PRIMARY KEY (`id`) USING BTREE,
                             INDEX `idx_pid`(`pid`) USING BTREE,
                             INDEX `idx_url`(`url`(191)) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2002 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'System Menu/Privilege Table' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_menu
-- ----------------------------
INSERT INTO `sys_menu` VALUES (1001, 1, 'list assignment', '/assignment/list', NULL, 'assignment:list', 2, 0, 0, 0, '2023-02-11 18:56:11', '2023-02-11 18:56:11');
INSERT INTO `sys_menu` VALUES (2001, 2, 'list grade', '/grade/list', NULL, 'grade:list', 2, 0, 0, 0, '2023-02-11 18:57:08', '2023-02-11 18:57:08');

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role`  (
                             `id` int(11) NOT NULL AUTO_INCREMENT,
                             `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'name',
                             `code` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Key',
                             `remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'role description',
                             `deleted` int(1) NOT NULL DEFAULT 0 COMMENT 'is deleted? 0-no 1-yes',
                             `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'createTime',
                             `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updateTime',
                             PRIMARY KEY (`id`) USING BTREE,
                             INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'role table' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES (1, 'super-admin', 'super-admin', 'has all permissions', 0, '2023-02-11 19:11:01', '2023-02-11 19:11:01');
INSERT INTO `sys_role` VALUES (2, 'admin', 'admin', 'partly permissions', 0, '2023-02-11 19:10:19', '2023-02-11 19:10:19');
INSERT INTO `sys_role` VALUES (3, 'staff', 'staff', 'staff', 0, '2023-02-11 19:10:21', '2023-02-11 19:10:21');
INSERT INTO `sys_role` VALUES (4, 'student', 'stu', 'student', 0, '2023-02-11 19:10:22', '2023-02-11 19:10:22');

-- ----------------------------
-- Table structure for sys_role_menu_mapping
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu_mapping`;
CREATE TABLE `sys_role_menu_mapping`  (
                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                          `roleId` int(11) NOT NULL COMMENT 'role id',
                                          `menuId` int(11) NOT NULL COMMENT 'permissions/menu id',
                                          `deleted` int(1) NOT NULL DEFAULT 0 COMMENT 'is deleted? 0-no 1-yes',
                                          `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'createTime',
                                          `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updateTime',
                                          PRIMARY KEY (`id`) USING BTREE,
                                          INDEX `idx_roleId`(`roleId`) USING BTREE,
                                          INDEX `idx_menuId`(`menuId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Role-permission relationship table' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_role_menu_mapping
-- ----------------------------
INSERT INTO `sys_role_menu_mapping` VALUES (1, 2, 1001, 0, '2023-02-11 19:02:43', '2023-02-11 19:02:43');
INSERT INTO `sys_role_menu_mapping` VALUES (2, 3, 1001, 0, '2023-02-11 19:02:56', '2023-02-11 19:02:56');
INSERT INTO `sys_role_menu_mapping` VALUES (3, 1, 1001, 0, '2023-02-11 19:03:01', '2023-02-11 19:03:01');
INSERT INTO `sys_role_menu_mapping` VALUES (4, 4, 1001, 0, '2023-02-11 19:03:06', '2023-02-11 19:03:06');
INSERT INTO `sys_role_menu_mapping` VALUES (5, 1, 2001, 0, '2023-02-11 19:03:13', '2023-02-11 19:03:13');
INSERT INTO `sys_role_menu_mapping` VALUES (6, 2, 2001, 0, '2023-02-11 19:03:17', '2023-02-11 19:03:17');
INSERT INTO `sys_role_menu_mapping` VALUES (7, 3, 2001, 0, '2023-02-11 19:03:23', '2023-02-11 19:03:23');

-- ----------------------------
-- Table structure for sys_user_role_mapping
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role_mapping`;
CREATE TABLE `sys_user_role_mapping`  (
                                          `id` int(11) NOT NULL AUTO_INCREMENT,
                                          `userId` int(11) NOT NULL COMMENT 'user id',
                                          `roleId` int(11) NOT NULL COMMENT 'role id',
                                          `deleted` int(1) NOT NULL DEFAULT 0 COMMENT 'is deleted? 0-no 1-yes',
                                          `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'createTime',
                                          `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updateTime',
                                          PRIMARY KEY (`id`) USING BTREE,
                                          INDEX `idx_userId`(`userId`) USING BTREE,
                                          INDEX `idx_roleId`(`roleId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'User-Role Relationship Table' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_user_role_mapping
-- ----------------------------
INSERT INTO `sys_user_role_mapping` VALUES (1, 1, 1, 0, '2023-02-11 19:00:23', '2023-02-11 19:00:23');
INSERT INTO `sys_user_role_mapping` VALUES (2, 2, 2, 0, '2023-02-11 19:00:29', '2023-02-11 19:00:29');
INSERT INTO `sys_user_role_mapping` VALUES (4, 7, 3, 0, '2023-02-11 19:02:10', '2023-02-11 19:02:10');
INSERT INTO `sys_user_role_mapping` VALUES (6, 24, 4, 0, '2023-02-11 20:03:19', '2023-02-11 20:03:19');
INSERT INTO `sys_user_role_mapping` VALUES (7, 25, 4, 0, '2023-02-27 16:50:55', '2023-02-27 16:50:55');
INSERT INTO `sys_user_role_mapping` VALUES (8, 26, 4, 0, '2023-02-27 16:51:06', '2023-02-27 16:51:06');
INSERT INTO `sys_user_role_mapping` VALUES (9, 27, 4, 0, '2023-02-27 16:51:15', '2023-02-27 16:51:15');
INSERT INTO `sys_user_role_mapping` VALUES (10, 28, 4, 0, '2023-02-27 16:51:19', '2023-02-27 16:51:19');
INSERT INTO `sys_user_role_mapping` VALUES (11, 29, 3, 0, '2023-02-27 16:52:23', '2023-02-27 16:52:23');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
                         `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                         `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
                         `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
                         `email` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                         `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                         `password` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
                         `avatar` varchar(2083) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
                         `salt` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
                         PRIMARY KEY (`id`) USING BTREE,
                         UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
/*
email: superadmin@u.nus.edu, password: superadmin
email: admin@u.nus.edu, password: admin
email: prof@u.nus.edu, password: prof
email: stu4@u.nus.edu, password: stu
email: stu5@u.nus.edu, password: stu
email: stu6@u.nus.edu, password: stu
email: stu1@u.nus.edu, password: stu
email: stu2@u.nus.edu, password: stu
email: ta1@u.nus.edu, password: ta
*/
INSERT INTO `user` VALUES (1, current_timestamp(), current_timestamp(), 'superadmin@u.nus.edu', 'super admin', 'B5B2E6F4F5F5A1CCC73F8F3B79E14479', NULL, '881006D5-393D-422C-8404-5173F29438CD');
INSERT INTO `user` VALUES (2, current_timestamp(), current_timestamp(), 'admin@u.nus.edu', 'admin', '8AF17CDD42B613F815CA9A423B1B37BC', NULL, 'A9EEC3CB-96BE-4FA9-BD71-B848EB6A8806');
INSERT INTO `user` VALUES (7, current_timestamp(), current_timestamp(), 'prof@u.nus.edu', 'prof', 'B96AA414289C748542F46480BD9DAFCA', NULL, '5E9D0ACB-0252-47C6-9014-CB3EAB8FFCD3');
INSERT INTO `user` VALUES (24, current_timestamp(), current_timestamp(), 'stu4@u.nus.edu', 'stu4', 'ED5CEA1C777D2E5271C6908EAF1B00ED', NULL, '798B974B-9D34-4AC5-8CCE-F5CF1783D129');
INSERT INTO `user` VALUES (25, current_timestamp(), current_timestamp(), 'stu5@u.nus.edu', 'stu5', 'A80C38ECC0BF4B15549571481057CCBF', NULL, '5B4A4943-E936-42E2-B3CC-61EFB15D0E31');
INSERT INTO `user` VALUES (26, current_timestamp(), current_timestamp(), 'stu6@u.nus.edu', 'stu6', 'B1ED8D00A252F87037CA5C9689A3E15A', NULL, '9929E0E1-0FC6-4BFA-A15F-6059622720CC');
INSERT INTO `user` VALUES (27, current_timestamp(), current_timestamp(), 'stu1@u.nus.edu', 'stu1', '881209FFEFCBC89648DB79E4C88A3F7F', NULL, '093D8719-73C6-43FE-B073-708D665949C2');
INSERT INTO `user` VALUES (28, current_timestamp(), current_timestamp(), 'stu2@u.nus.edu', 'stu2', '7E130BBB26AAA06D8B50E626B0BEB17C', NULL, 'A6D245D6-2DDE-40B3-A306-D54DC8357582');
INSERT INTO `user` VALUES (29, current_timestamp(), current_timestamp(), 'ta1@u.nus.edu', 'ta1', '3FB4A44BB702529FAC1909ACE71D13C5', NULL, '05389649-3B19-40DD-9B9C-3BDAA97E8C02');


DROP TABLE IF EXISTS `final_grade`;
CREATE TABLE `final_grade` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `assignment_id` int(11) NOT NULL,
    `stu_id` int(11) NOT NULL,
    `final_grade` float DEFAULT '0',
    `peer_grades` varchar(255) DEFAULT NULL COMMENT 'grade1, grade2, grade3..',
    `participation_grade` float DEFAULT NULL,
    `percentage_grade` float NOT NULL COMMENT 'avg(peer_grades) / total_grade_in_rubric',
    `actual_grade` float NOT NULL,
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `grade_calculation_config`;
CREATE TABLE `grade_calculation_config` (
    `assignment_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `include_participation` tinyint(1) DEFAULT '0' COMMENT '0: not include participation marks\n1: include',
    `ptc_weight` FLOAT DEFAULT '0' COMMENT 'paticipation weights, final grade = ptc_weight * ptc + (1-ptc_weight) * avg_peer_grade',
    `include_outliers` tinyint(1) DEFAULT '1' COMMENT '0: detect and remove outliers\n1: include outliers\n',
    `include_highest` tinyint(1) DEFAULT '1' COMMENT '0: exclude highest marks\n1: include highest marks',
    `include_lowest` tinyint(1) DEFAULT '1' COMMENT '0: exclude lowest marks\n1: include lowest marks',
    PRIMARY KEY (`assignment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

BEGIN;
INSERT INTO `grade_calculation_config` (`assignment_id`, `include_participation`, `ptc_weight`, `include_outliers`, `include_highest`, `include_lowest`) VALUES (2, 0, 0, 1, 1, 1);
COMMIT;



SET FOREIGN_KEY_CHECKS = 1;
